Procrastinate Pro+

Маркетинговый анализ развлекательного приложения Procrastinate Pro+.

Проблема: несмотря на огромные вложения в рекламу, последние несколько месяцев компания терпит убытки.

Задача: разобраться в причинах и помочь компании выйти в плюс.

Описание данных:

Есть данные о пользователях, привлечённых с 1 мая по 27 октября 2019 года:

• лог сервера с данными об их посещениях
• выгрузка их покупок за этот период
• рекламные расходы

Условие:

На календаре 1 ноября 2019 года, а в бизнес-плане заложено, что пользователи должны окупаться не позднее чем через две недели после привлечения

Table of Contents

• 1  Загрузка и предобратка данных
• 2  Функции для расчёта и анализа LTV, ROI, удержания и конверсии
• 3  Исследовательский анализ данных
• 4  Маркетинг
• 5  Окупаемость рекламы
• 6  Вывод
  • 6.1  Подпункт

Загрузка и предобратка данных

import pandas as pd
import seaborn as sns
import numpy as np
import matplotlib.pyplot as plt
from datetime import datetime, timedelta

try:
    visits = pd.read_csv('visits_info_short.csv')
except:
    visits = pd.read_csv('/datasets/visits_info_short.csv')

try:
    orders = pd.read_csv('orders_info_short.csv')
except:
    orders = pd.read_csv('/datasets/orders_info_short.csv')
    
try:
    costs = pd.read_csv('costs_info_short.csv')
except:
    costs = pd.read_csv('/datasets/costs_info_short.csv')

display(visits)
display(orders)
display(costs)

	User Id	Region	Device	Channel	Session Start	Session End
0	981449118918	United States	iPhone	organic	2019-05-01 02:36:01	2019-05-01 02:45:01
1	278965908054	United States	iPhone	organic	2019-05-01 04:46:31	2019-05-01 04:47:35
2	590706206550	United States	Mac	organic	2019-05-01 14:09:25	2019-05-01 15:32:08
3	326433527971	United States	Android	TipTop	2019-05-01 00:29:59	2019-05-01 00:54:25
4	349773784594	United States	Mac	organic	2019-05-01 03:33:35	2019-05-01 03:57:40
...	...	...	...	...	...	...
309896	329994900775	UK	PC	LeapBob	2019-10-31 13:28:12	2019-10-31 14:39:29
309897	334903592310	France	PC	lambdaMediaAds	2019-10-31 22:14:52	2019-10-31 22:39:36
309898	540102010126	Germany	PC	organic	2019-10-31 01:40:48	2019-10-31 01:41:31
309899	308736936846	Germany	Mac	organic	2019-10-31 07:37:34	2019-10-31 07:37:55
309900	109329042535	Germany	PC	lambdaMediaAds	2019-10-31 14:17:43	2019-10-31 15:17:04

309901 rows × 6 columns

	User Id	Event Dt	Revenue
0	188246423999	2019-05-01 23:09:52	4.99
1	174361394180	2019-05-01 12:24:04	4.99
2	529610067795	2019-05-01 11:34:04	4.99
3	319939546352	2019-05-01 15:34:40	4.99
4	366000285810	2019-05-01 13:59:51	4.99
...	...	...	...
40207	651604369137	2019-10-31 16:19:07	4.99
40208	275341387049	2019-10-31 01:17:17	4.99
40209	374656616484	2019-10-31 06:17:29	4.99
40210	168548862926	2019-10-31 22:46:19	4.99
40211	329994900775	2019-10-31 13:29:06	4.99

40212 rows × 3 columns

	dt	Channel	costs
0	2019-05-01	FaceBoom	113.3
1	2019-05-02	FaceBoom	78.1
2	2019-05-03	FaceBoom	85.8
3	2019-05-04	FaceBoom	136.4
4	2019-05-05	FaceBoom	122.1
...	...	...	...
1795	2019-10-23	lambdaMediaAds	4.0
1796	2019-10-24	lambdaMediaAds	6.4
1797	2019-10-25	lambdaMediaAds	8.8
1798	2019-10-26	lambdaMediaAds	8.8
1799	2019-10-27	lambdaMediaAds	12.0

1800 rows × 3 columns

Приведем названия колонок к рабочему виду

visits.columns = visits.columns.str.lower()
visits = visits.rename(columns = {'user id' : 'user_id', 'session start' : 'session_start', 'session end' : 'session_end'})
orders.columns = orders.columns.str.lower()
orders = orders.rename(columns = {'user id' : 'user_id', 'event dt' : 'event_dt'})
costs.columns = costs.columns.str.lower()

visits.info()
orders.info()
costs.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 309901 entries, 0 to 309900
Data columns (total 6 columns):
 #   Column         Non-Null Count   Dtype 
---  ------         --------------   ----- 
 0   user_id        309901 non-null  int64 
 1   region         309901 non-null  object
 2   device         309901 non-null  object
 3   channel        309901 non-null  object
 4   session_start  309901 non-null  object
 5   session_end    309901 non-null  object
dtypes: int64(1), object(5)
memory usage: 14.2+ MB
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 40212 entries, 0 to 40211
Data columns (total 3 columns):
 #   Column    Non-Null Count  Dtype  
---  ------    --------------  -----  
 0   user_id   40212 non-null  int64  
 1   event_dt  40212 non-null  object 
 2   revenue   40212 non-null  float64
dtypes: float64(1), int64(1), object(1)
memory usage: 942.6+ KB
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1800 entries, 0 to 1799
Data columns (total 3 columns):
 #   Column   Non-Null Count  Dtype  
---  ------   --------------  -----  
 0   dt       1800 non-null   object 
 1   channel  1800 non-null   object 
 2   costs    1800 non-null   float64
dtypes: float64(1), object(2)
memory usage: 42.3+ KB

Пропусков нет. Типы данных, за исключением дат, в соответствующем формате

display(visits.duplicated().sum())
display(orders.duplicated().sum())
display(costs.duplicated().sum())

0

0

0

Явных дубликатов не обнаружено, все ок

display(visits['channel'].value_counts())
display(visits['device'].value_counts())
display(visits['region'].value_counts())
display(costs['channel'].value_counts())

organic               107760
TipTop                 54794
FaceBoom               49022
WahooNetBanner         20465
LeapBob                17013
OppleCreativeMedia     16794
RocketSuperAds         12724
YRabbit                 9053
MediaTornado            8878
AdNonSense              6891
lambdaMediaAds          6507
Name: channel, dtype: int64

iPhone     112603
Android     72590
PC          62686
Mac         62022
Name: device, dtype: int64

United States    207327
UK                36419
France            35396
Germany           30759
Name: region, dtype: int64

LeapBob               180
TipTop                180
WahooNetBanner        180
FaceBoom              180
MediaTornado          180
lambdaMediaAds        180
AdNonSense            180
RocketSuperAds        180
YRabbit               180
OppleCreativeMedia    180
Name: channel, dtype: int64

Неявные дубликаты тоже отсутствуют

visits['session_start'] = pd.to_datetime(visits['session_start'])
visits['session_end'] = pd.to_datetime(visits['session_end'])
orders['event_dt'] = pd.to_datetime(orders['event_dt'])
costs['dt'] = pd.to_datetime(costs['dt']).dt.date

Приведем даты к формату datetime

Функции для расчёта и анализа LTV, ROI, удержания и конверсии

Добавим необходимые функции, полученные в ходе обучения в спринте, слегка изменив их

# функция для создания пользовательских профилей

def get_profiles(sessions, orders, ad_costs):

    # находим параметры первых посещений
    profiles = (
        sessions.sort_values(by=['user_id', 'session_start'])
        .groupby('user_id')
        .agg(
            {
                'session_start': 'first',
                'channel': 'first',
                'device': 'first',
                'region': 'first',
            }
        )
        .rename(columns={'session_start': 'first_ts'})
        .reset_index()
    )

    # для когортного анализа определяем дату первого посещения
    # и первый день месяца, в который это посещение произошло
    profiles['dt'] = profiles['first_ts'].dt.date
    profiles['month'] = profiles['first_ts'].astype('datetime64[M]')

    # добавляем признак платящих пользователей
    profiles['payer'] = profiles['user_id'].isin(orders['user_id'].unique())

    

    # считаем количество уникальных пользователей
    # с одинаковыми источником и датой привлечения
    new_users = (
        profiles.groupby(['dt', 'channel'])
        .agg({'user_id': 'nunique'})
        .rename(columns={'user_id': 'unique_users'})
        .reset_index()
    )

    # объединяем траты на рекламу и число привлечённых пользователей
    ad_costs = ad_costs.merge(new_users, on=['dt', 'channel'], how='left')

    # делим рекламные расходы на число привлечённых пользователей
    ad_costs['acquisition_cost'] = ad_costs['costs'] / ad_costs['unique_users']

    # добавляем стоимость привлечения в профили
    profiles = profiles.merge(
        ad_costs[['dt', 'channel', 'acquisition_cost']],
        on=['dt', 'channel'],
        how='left',
    )

    # стоимость привлечения органических пользователей равна нулю
    profiles['acquisition_cost'] = profiles['acquisition_cost'].fillna(0)

    return profiles

# функция для расчёта удержания

def get_retention(
    profiles,
    sessions,
    observation_date,
    horizon_days,
    dimensions=[],
    ignore_horizon=False,
):

    # добавляем столбец payer в передаваемый dimensions список
    dimensions = ['payer'] + dimensions

    # исключаем пользователей, не «доживших» до горизонта анализа
    last_suitable_acquisition_date = observation_date
    if not ignore_horizon:
        last_suitable_acquisition_date = observation_date - timedelta(
            days=horizon_days - 1
        )
    result_raw = profiles.query('dt <= @last_suitable_acquisition_date')

    # собираем «сырые» данные для расчёта удержания
    result_raw = result_raw.merge(
        sessions[['user_id', 'session_start']], on='user_id', how='left'
    )
    result_raw['lifetime'] = (
        result_raw['session_start'] - result_raw['first_ts']
    ).dt.days

    # функция для группировки таблицы по желаемым признакам
    def group_by_dimensions(df, dims, horizon_days):
        result = df.pivot_table(
            index=dims, columns='lifetime', values='user_id', aggfunc='nunique'
        )
        cohort_sizes = (
            df.groupby(dims)
            .agg({'user_id': 'nunique'})
            .rename(columns={'user_id': 'cohort_size'})
        )
        result = cohort_sizes.merge(result, on=dims, how='left').fillna(0)
        result = result.div(result['cohort_size'], axis=0)
        result = result[['cohort_size'] + list(range(horizon_days))]
        result['cohort_size'] = cohort_sizes
        return result

    # получаем таблицу удержания
    result_grouped = group_by_dimensions(result_raw, dimensions, horizon_days)

    # получаем таблицу динамики удержания
    result_in_time = group_by_dimensions(
        result_raw, dimensions + ['dt'], horizon_days
    )

    # возвращаем обе таблицы и сырые данные
    return result_raw, result_grouped, result_in_time 

# функция для расчёта конверсии

def get_conversion(
    profiles,
    purchases,
    observation_date,
    horizon_days,
    dimensions=[],
    ignore_horizon=False,
):

    # исключаем пользователей, не «доживших» до горизонта анализа
    last_suitable_acquisition_date = observation_date
    if not ignore_horizon:
        last_suitable_acquisition_date = observation_date - timedelta(
            days=horizon_days - 1
        )
    result_raw = profiles.query('dt <= @last_suitable_acquisition_date')

    # определяем дату и время первой покупки для каждого пользователя
    first_purchases = (
        purchases.sort_values(by=['user_id', 'event_dt'])
        .groupby('user_id')
        .agg({'event_dt': 'first'})
        .reset_index()
    )

    # добавляем данные о покупках в профили
    result_raw = result_raw.merge(
        first_purchases[['user_id', 'event_dt']], on='user_id', how='left'
    )

    # рассчитываем лайфтайм для каждой покупки
    result_raw['lifetime'] = (
        result_raw['event_dt'] - result_raw['first_ts']
    ).dt.days

    # группируем по cohort, если в dimensions ничего нет
    if len(dimensions) == 0:
        result_raw['cohort'] = 'All users' 
        dimensions = dimensions + ['cohort']

    # функция для группировки таблицы по желаемым признакам
    def group_by_dimensions(df, dims, horizon_days):
        result = df.pivot_table(
            index=dims, columns='lifetime', values='user_id', aggfunc='nunique'
        )
        result = result.fillna(0).cumsum(axis = 1)
        cohort_sizes = (
            df.groupby(dims)
            .agg({'user_id': 'nunique'})
            .rename(columns={'user_id': 'cohort_size'})
        )
        result = cohort_sizes.merge(result, on=dims, how='left').fillna(0)
        # делим каждую «ячейку» в строке на размер когорты
        # и получаем conversion rate
        result = result.div(result['cohort_size'], axis=0)
        result = result[['cohort_size'] + list(range(horizon_days))]
        result['cohort_size'] = cohort_sizes
        return result

    # получаем таблицу конверсии
    result_grouped = group_by_dimensions(result_raw, dimensions, horizon_days)

    # для таблицы динамики конверсии убираем 'cohort' из dimensions
    if 'cohort' in dimensions: 
        dimensions = []

    # получаем таблицу динамики конверсии
    result_in_time = group_by_dimensions(
        result_raw, dimensions + ['dt'], horizon_days
    )

    # возвращаем обе таблицы и сырые данные
    return result_raw, result_grouped, result_in_time 

# функция для расчёта LTV и ROI

def get_ltv(
    profiles,
    purchases,
    observation_date,
    horizon_days,
    dimensions=[],
    ignore_horizon=False,
):

    # исключаем пользователей, не «доживших» до горизонта анализа
    last_suitable_acquisition_date = observation_date
    if not ignore_horizon:
        last_suitable_acquisition_date = observation_date - timedelta(
            days=horizon_days - 1
        )
    result_raw = profiles.query('dt <= @last_suitable_acquisition_date')
    # добавляем данные о покупках в профили
    result_raw = result_raw.merge(
        purchases[['user_id', 'event_dt', 'revenue']], on='user_id', how='left'
    )
    # рассчитываем лайфтайм пользователя для каждой покупки
    result_raw['lifetime'] = (
        result_raw['event_dt'] - result_raw['first_ts']
    ).dt.days
    # группируем по cohort, если в dimensions ничего нет
    if len(dimensions) == 0:
        result_raw['cohort'] = 'All users'
        dimensions = dimensions + ['cohort']

    # функция группировки по желаемым признакам
    def group_by_dimensions(df, dims, horizon_days):
        # строим «треугольную» таблицу выручки
        result = df.pivot_table(
            index=dims, columns='lifetime', values='revenue', aggfunc='sum'
        )
        # находим сумму выручки с накоплением
        result = result.fillna(0).cumsum(axis=1)
        # вычисляем размеры когорт
        cohort_sizes = (
            df.groupby(dims)
            .agg({'user_id': 'nunique'})
            .rename(columns={'user_id': 'cohort_size'})
        )
        # объединяем размеры когорт и таблицу выручки
        result = cohort_sizes.merge(result, on=dims, how='left').fillna(0)
        # считаем LTV: делим каждую «ячейку» в строке на размер когорты
        result = result.div(result['cohort_size'], axis=0)
        # исключаем все лайфтаймы, превышающие горизонт анализа
        result = result[['cohort_size'] + list(range(horizon_days))]
        # восстанавливаем размеры когорт
        result['cohort_size'] = cohort_sizes

        # собираем датафрейм с данными пользователей и значениями CAC, 
        # добавляя параметры из dimensions
        cac = df[['user_id', 'acquisition_cost'] + dims].drop_duplicates()

        # считаем средний CAC по параметрам из dimensions
        cac = (
            cac.groupby(dims)
            .agg({'acquisition_cost': 'mean'})
            .rename(columns={'acquisition_cost': 'cac'})
        )

        # считаем ROI: делим LTV на CAC
        roi = result.div(cac['cac'], axis=0)

        # удаляем строки с бесконечным ROI
        roi = roi[~roi['cohort_size'].isin([np.inf])]

        # восстанавливаем размеры когорт в таблице ROI
        roi['cohort_size'] = cohort_sizes

        # добавляем CAC в таблицу ROI
        roi['cac'] = cac['cac']

        # в финальной таблице оставляем размеры когорт, CAC
        # и ROI в лайфтаймы, не превышающие горизонт анализа
        roi = roi[['cohort_size', 'cac'] + list(range(horizon_days))]

        # возвращаем таблицы LTV и ROI
        return result, roi

    # получаем таблицы LTV и ROI
    result_grouped, roi_grouped = group_by_dimensions(
        result_raw, dimensions, horizon_days
    )

    # для таблиц динамики убираем 'cohort' из dimensions
    if 'cohort' in dimensions:
        dimensions = []

    # получаем таблицы динамики LTV и ROI
    result_in_time, roi_in_time = group_by_dimensions(
        result_raw, dimensions + ['dt'], horizon_days
    )

    return (
        result_raw,  # сырые данные
        result_grouped,  # таблица LTV
        result_in_time,  # таблица динамики LTV
        roi_grouped,  # таблица ROI
        roi_in_time,  # таблица динамики ROI
    ) 

# функция для сглаживания фрейма

def filter_data(df, window):
    # для каждого столбца применяем скользящее среднее
    for column in df.columns.values:
        df[column] = df[column].rolling(window).mean() 
    return df 

# функция для визуализации удержания

def plot_retention(retention, retention_history, horizon, window=7):

    # задаём размер сетки для графиков
    plt.figure(figsize=(15, 10))

    # исключаем размеры когорт и удержание первого дня
    retention = retention.drop(columns=['cohort_size', 0])
    # в таблице динамики оставляем только нужный лайфтайм
    retention_history = retention_history.drop(columns=['cohort_size'])[
        [horizon - 1]
    ]

    # если в индексах таблицы удержания только payer,
    # добавляем второй признак — cohort
    if retention.index.nlevels == 1:
        retention['cohort'] = 'All users'
        retention = retention.reset_index().set_index(['cohort', 'payer'])

    # в таблице графиков — два столбца и две строки, четыре ячейки
    # в первой строим кривые удержания платящих пользователей
    ax1 = plt.subplot(2, 2, 1)
    retention.query('payer == True').droplevel('payer').T.plot(
        grid=True, ax=ax1
    )
    plt.legend()
    plt.xlabel('Лайфтайм')
    plt.title('Удержание платящих пользователей')

    # во второй ячейке строим кривые удержания неплатящих
    # вертикальная ось — от графика из первой ячейки
    ax2 = plt.subplot(2, 2, 2, sharey=ax1)
    retention.query('payer == False').droplevel('payer').T.plot(
        grid=True, ax=ax2
    )
    plt.legend()
    plt.xlabel('Лайфтайм')
    plt.title('Удержание неплатящих пользователей')

    # в третьей ячейке — динамика удержания платящих
    ax3 = plt.subplot(2, 2, 3)
    # получаем названия столбцов для сводной таблицы
    columns = [
        name
        for name in retention_history.index.names
        if name not in ['dt', 'payer']
    ]
    # фильтруем данные и строим график
    filtered_data = retention_history.query('payer == True').pivot_table(
        index='dt', columns=columns, values=horizon - 1, aggfunc='mean'
    )
    filter_data(filtered_data, window).plot(grid=True, ax=ax3)
    plt.xlabel('Дата привлечения')
    plt.title(
        'Динамика удержания платящих пользователей на {}-й день'.format(
            horizon
        )
    )

    # в чётвертой ячейке — динамика удержания неплатящих
    ax4 = plt.subplot(2, 2, 4, sharey=ax3)
    # фильтруем данные и строим график
    filtered_data = retention_history.query('payer == False').pivot_table(
        index='dt', columns=columns, values=horizon - 1, aggfunc='mean'
    )
    filter_data(filtered_data, window).plot(grid=True, ax=ax4)
    plt.xlabel('Дата привлечения')
    plt.title(
        'Динамика удержания неплатящих пользователей на {}-й день'.format(
            horizon
        )
    )
    
    plt.tight_layout()
    plt.show() 

# функция для визуализации конверсии

def plot_conversion(conversion, conversion_history, horizon, window=7):

    # задаём размер сетки для графиков
    plt.figure(figsize=(15, 5))

    # исключаем размеры когорт
    conversion = conversion.drop(columns=['cohort_size'])
    # в таблице динамики оставляем только нужный лайфтайм
    conversion_history = conversion_history.drop(columns=['cohort_size'])[
        [horizon - 1]
    ]

    # первый график — кривые конверсии
    ax1 = plt.subplot(1, 2, 1)
    conversion.T.plot(grid=True, ax=ax1)
    plt.legend()
    plt.xlabel('Лайфтайм')
    plt.title('Конверсия пользователей')

    # второй график — динамика конверсии
    ax2 = plt.subplot(1, 2, 2, sharey=ax1)
    columns = [
        # столбцами сводной таблицы станут все столбцы индекса, кроме даты
        name for name in conversion_history.index.names if name not in ['dt']
    ]
    filtered_data = conversion_history.pivot_table(
        index='dt', columns=columns, values=horizon - 1, aggfunc='mean'
    )
    filter_data(filtered_data, window).plot(grid=True, ax=ax2)
    plt.xlabel('Дата привлечения')
    plt.title('Динамика конверсии пользователей на {}-й день'.format(horizon))

    plt.tight_layout()
    plt.show() 

# функция для визуализации LTV и ROI

def plot_ltv_roi(ltv, ltv_history, roi, roi_history, horizon, window=7):

    # задаём сетку отрисовки графиков
    plt.figure(figsize=(20, 10))

    # из таблицы ltv исключаем размеры когорт
    ltv = ltv.drop(columns=['cohort_size'])
    # в таблице динамики ltv оставляем только нужный лайфтайм
    ltv_history = ltv_history.drop(columns=['cohort_size'])[[horizon - 1]]

    # стоимость привлечения запишем в отдельный фрейм
    cac_history = roi_history[['cac']]

    # из таблицы roi исключаем размеры когорт и cac
    roi = roi.drop(columns=['cohort_size', 'cac'])
    # в таблице динамики roi оставляем только нужный лайфтайм
    roi_history = roi_history.drop(columns=['cohort_size', 'cac'])[
        [horizon - 1]
    ]

    # первый график — кривые ltv
    ax1 = plt.subplot(2, 3, 1)
    ltv.T.plot(grid=True, ax=ax1)
    plt.legend()
    plt.xlabel('Лайфтайм')
    plt.title('LTV')

    # второй график — динамика ltv
    ax2 = plt.subplot(2, 3, 2, sharey=ax1)
    # столбцами сводной таблицы станут все столбцы индекса, кроме даты
    columns = [name for name in ltv_history.index.names if name not in ['dt']]
    filtered_data = ltv_history.pivot_table(
        index='dt', columns=columns, values=horizon - 1, aggfunc='mean'
    )
    filter_data(filtered_data, window).plot(grid=True, ax=ax2)
    plt.xlabel('Дата привлечения')
    plt.title('Динамика LTV пользователей на {}-й день'.format(horizon))

    # третий график — динамика cac
    ax3 = plt.subplot(2, 3, 3, sharey=ax1)
    # столбцами сводной таблицы станут все столбцы индекса, кроме даты
    columns = [name for name in cac_history.index.names if name not in ['dt']]
    filtered_data = cac_history.pivot_table(
        index='dt', columns=columns, values='cac', aggfunc='mean'
    )
    filter_data(filtered_data, window).plot(grid=True, ax=ax3)
    plt.xlabel('Дата привлечения')
    plt.title('Динамика стоимости привлечения пользователей')

    # четвёртый график — кривые roi
    ax4 = plt.subplot(2, 3, 4)
    roi.T.plot(grid=True, ax=ax4)
    plt.axhline(y=1, color='red', linestyle='--', label='Уровень окупаемости')
    plt.legend()
    plt.xlabel('Лайфтайм')
    plt.title('ROI')

    # пятый график — динамика roi
    ax5 = plt.subplot(2, 3, 5, sharey=ax4)
    # столбцами сводной таблицы станут все столбцы индекса, кроме даты
    columns = [name for name in roi_history.index.names if name not in ['dt']]
    filtered_data = roi_history.pivot_table(
        index='dt', columns=columns, values=horizon - 1, aggfunc='mean'
    )
    filter_data(filtered_data, window).plot(grid=True, ax=ax5)
    plt.axhline(y=1, color='red', linestyle='--', label='Уровень окупаемости')
    plt.xlabel('Дата привлечения')
    plt.title('Динамика ROI пользователей на {}-й день'.format(horizon))

    plt.tight_layout()
    plt.show() 

Исследовательский анализ данных

Получим профили пользователей

profiles = get_profiles(visits, orders, costs)
profiles

	user_id	first_ts	channel	device	region	dt	month	payer	acquisition_cost
0	599326	2019-05-07 20:58:57	FaceBoom	Mac	United States	2019-05-07	2019-05-01	True	1.088172
1	4919697	2019-07-09 12:46:07	FaceBoom	iPhone	United States	2019-07-09	2019-07-01	False	1.107237
2	6085896	2019-10-01 09:58:33	organic	iPhone	France	2019-10-01	2019-10-01	False	0.000000
3	22593348	2019-08-22 21:35:48	AdNonSense	PC	Germany	2019-08-22	2019-08-01	False	0.988235
4	31989216	2019-10-02 00:07:44	YRabbit	iPhone	United States	2019-10-02	2019-10-01	False	0.230769
...	...	...	...	...	...	...	...	...	...
150003	999956196527	2019-09-28 08:33:02	TipTop	iPhone	United States	2019-09-28	2019-09-01	False	3.500000
150004	999975439887	2019-10-21 00:35:17	organic	PC	UK	2019-10-21	2019-10-01	False	0.000000
150005	999976332130	2019-07-23 02:57:06	TipTop	iPhone	United States	2019-07-23	2019-07-01	False	2.600000
150006	999979924135	2019-09-28 21:28:09	MediaTornado	PC	United States	2019-09-28	2019-09-01	False	0.205714
150007	999999563947	2019-10-18 19:57:25	organic	iPhone	United States	2019-10-18	2019-10-01	False	0.000000

150008 rows × 9 columns

Определим минимальную и максимальную даты привлечения пользователей

min_date = profiles['dt'].min()
max_date = profiles['dt'].max()
print(f' Минимальная дата привлечения: {min_date} \n Максимальная дата привлечения: {max_date}')

 Минимальная дата привлечения: 2019-05-01 
 Максимальная дата привлечения: 2019-10-27

Даты привлечения пользователей, полученные из датафрейма, соответствуют условиям проекта

Выясним, из каких стран пользователи приходят в приложение и на какую страну приходится больше всего платящих пользователей

(profiles.groupby('region').agg({'user_id' : 'count', 'payer' : 'mean'}).
     rename(columns = {'user_id' : 'count', 'payer' : 'mean'}).
         sort_values(by = 'mean', ascending = False).
             style.format({'count' : '{:.0f}', 'mean': '{:,.2%}'.format}))

	count	mean
region
United States	100002	6.90%
Germany	14981	4.11%
UK	17575	3.98%
France	17450	3.80%

Большая часть пользователей приложения из США. Среди жителей Америки также наибольшая доля платящих пользователей

Узнаем, какими устройствами пользуются клиенты и какие устройства предпочитают платящие пользователи

(profiles.groupby('device').agg({'user_id' : 'count', 'payer' : 'mean'}).
     rename(columns = {'user_id' : 'count', 'payer' : 'mean'}).
         sort_values(by = 'mean', ascending = False).
             style.format({'count' : '{:.0f}', 'mean': '{:,.2%}'.format}))

	count	mean
device
Mac	30042	6.36%
iPhone	54479	6.21%
Android	35032	5.85%
PC	30455	5.05%

Посетителей с айфонами больше всего. Также пользователи apple предпочитают платить немногим больше, нежели пользователи android или ПК

Изучим рекламные источники привлечения и определим каналы, из которых пришло больше всего платящих пользователей

(profiles.groupby('channel').agg({'user_id' : 'count', 'payer' : 'mean'}).
     rename(columns = {'user_id' : 'count', 'payer' : 'mean'}).
         sort_values(by = 'mean', ascending = False).
             style.format({'count' : '{:.0f}', 'mean': '{:,.2%}'.format}))

	count	mean
channel
FaceBoom	29144	12.20%
AdNonSense	3880	11.34%
lambdaMediaAds	2149	10.47%
TipTop	19561	9.60%
RocketSuperAds	4448	7.91%
WahooNetBanner	8553	5.30%
YRabbit	4312	3.83%
MediaTornado	4364	3.57%
LeapBob	8553	3.06%
OppleCreativeMedia	8605	2.71%
organic	56439	2.06%

Больше всего пришло пользователей из FaceBoom и TipTop. Самую высокую конверсию показывают Faceboom, AdNonSense и lambdaMediaAds

Маркетинг

Посчитаем общую сумму расходов на маркетинг

Выясним, как траты распределены по рекламным источникам

total_costs = costs['costs'].sum()
cost_by_channel = costs.groupby('channel').agg({'costs' : 'sum'}).sort_values(by = 'costs', ascending = False)
cost_by_channel['percent'] = cost_by_channel['costs'] / total_costs
cost_by_channel = cost_by_channel.style.format({'costs' : '{:.2f}','percent': '{:,.2%}'.format})
print(f'Общая сумма расходов на маркетинг:  {total_costs.round()} $')
cost_by_channel

Общая сумма расходов на маркетинг:  105497.0 $

	costs	percent
channel
TipTop	54751.30	51.90%
FaceBoom	32445.60	30.75%
WahooNetBanner	5151.00	4.88%
AdNonSense	3911.25	3.71%
OppleCreativeMedia	2151.25	2.04%
RocketSuperAds	1833.00	1.74%
LeapBob	1797.60	1.70%
lambdaMediaAds	1557.60	1.48%
MediaTornado	954.48	0.90%
YRabbit	944.22	0.90%

Чуть больше половины всего рекламного бюджета ушло в TipTop.

Построим визуализацию динамики изменения расходов во времени по каждому источнику

(costs.pivot_table(index = 'dt', columns = 'channel', values = 'costs', aggfunc = 'mean').
     plot(figsize = (25, 10), grid = True))
plt.ylabel('Расходы на маркетинг, $')
plt.xlabel('Дата привлечения')
plt.title('Динамика расходов по каналам привлечения')
plt.show()

costs['week'] = pd.to_datetime(costs['dt']).dt.isocalendar().week
(costs.pivot_table(index = 'week', columns = 'channel', values = 'costs', aggfunc = 'mean').
     plot(figsize = (25, 10), grid = True))
xtick_location = costs['week'].tolist()[::1]
plt.ylabel('Расходы на маркетинг, $')
plt.xlabel('Номер недели привлечения')
plt.title('Динамика расходов по каналам привлечения по неделям')
plt.xticks(ticks=xtick_location)
plt.show()

На графике видно, что с Июня сильно выросли маркетинговые расходы в TipTop и Faceboom

Узнаем, сколько в среднем стоило привлечение одного пользователя (CAC) из каждого источника

(profiles.groupby('channel').agg({'acquisition_cost' : 'mean'}).
 sort_values(by = 'acquisition_cost', ascending = False).
    plot(kind = 'bar', grid = True, legend = None))
plt.ylabel('Стоимость привлечения одного клиента, $')
plt.xlabel('Канал привлечения')
plt.title('Стоимость привлечения одного клиента по каналам привлечения')
plt.xticks(rotation=50, horizontalalignment='right')
plt.show()

Привлечение одного пользователя из TipTop значительно дороже привлечения из всех остальных каналов.

Окупаемость рекламы

По условию дата анализа - 2019-11-01, а срок окупаемости привлечения одного клиента - 14 дней

observation_date = datetime(2019, 11, 1).date()
analysis_horizon = 14
profiles = profiles.query('channel != "organic"')

Построим графики LTV и ROI, а также графики динамики LTV, CAC и ROI

ltv_raw, ltv, ltv_history, roi, roi_history = get_ltv(
    profiles,
    orders,
    observation_date,
    analysis_horizon,
    ignore_horizon=False,
)
plot_ltv_roi(ltv, ltv_history, roi, roi_history, analysis_horizon, window=7)

По графикам можно сделать такие выводы:

• Реклама не окупается. ROI в конце двух недель — около 80%.
• CAC резко растет с Июня месяца, а ROI в тот же период начинает снижаться. Вероятно, дело в чрезмерном увеличении рекламного бюджета.
• LTV достаточно стабилен. Значит, дело не в ухудшении качества пользователей.

Построим и изучим графики конверсии и удержания

conversion_raw, conversion, conversion_history = get_conversion(profiles, 
                                                                orders, 
                                                                observation_date, 
                                                                analysis_horizon, 
                                                                ignore_horizon=False)
plot_conversion(conversion, conversion_history, analysis_horizon, window=7)

После увеличения рекламного бюджета в Июне показатель конверсии заметно вырос

retention_raw, retention, retention_history = get_retention(profiles, 
                                                            visits, 
                                                            observation_date, 
                                                            analysis_horizon,
                                                            ignore_horizon=False)
plot_retention(retention, retention_history, analysis_horizon, window=7)

Динамика удержания пользователей довольно стабильна Чтобы разобраться в причинах неокупаемости рекламы, пройдём по всем доступным характеристикам пользователей — стране, источнику и устройству первого посещения.

Начнем с разбивки по типу устройства

dimensions = ['device']
ltv_raw, ltv, ltv_history, roi, roi_history = get_ltv(
    profiles,
    orders,
    observation_date,
    analysis_horizon,
    dimensions = dimensions,
    ignore_horizon=False,
)
plot_ltv_roi(ltv, ltv_history, roi, roi_history, analysis_horizon, window=7)

После увелечения расходов на маркетинг, привлечение пользователей Iphone и Mac стало значительно дороже, чем пользователей Android и ПК. После двух недель, вторая группа достигает чуть больших показателей ROI, в то время как яблочники не преодолевают отметку в 80%. Вряд ли маркетологов устраивает такая окупаемость. Также с Июня ROI стабильно снижается для всех типов устройств

Посмотрим в разбике по странам

dimensions = ['region']
ltv_raw, ltv, ltv_history, roi, roi_history = get_ltv(
    profiles,
    orders,
    observation_date,
    analysis_horizon,
    dimensions = dimensions,
    ignore_horizon=False,
)
plot_ltv_roi(ltv, ltv_history, roi, roi_history, analysis_horizon, window=7)

Вот и первая проблема. Пользователи из США обходятся слишком дорого и не окупаются за 2 недели, в то время как пользователи из других стран окупаются уже через одну неделю. После увеличения затрат на рекламу, ROI пользователей из штатов не преодолевают отметку в 100%, даже несмотря на то, что их LTV выше чем у пользователей из других стран

conversion_raw, conversion, conversion_history = get_conversion(profiles, 
                                                                orders, 
                                                                observation_date, 
                                                                analysis_horizon, 
                                                                dimensions = dimensions,
                                                                ignore_horizon=False)
plot_conversion(conversion, conversion_history, analysis_horizon, window=7)

retention_raw, retention, retention_history = get_retention(profiles, 
                                                            visits, 
                                                            observation_date, 
                                                            analysis_horizon, 
                                                            dimensions = dimensions,
                                                            ignore_horizon=False)
plot_retention(retention, retention_history, analysis_horizon, window=7)

Увеличив в Июне расходы на привлечение пользователей из США, действительно удалось получить хорошие показатели конверсии, но удержание значительно хуже по сравнению с остальными

Посмотрим на разбивку по источникам привлечения

dimensions = ['channel']
ltv_raw, ltv, ltv_history, roi, roi_history = get_ltv(
    profiles,
    orders,
    observation_date,
    analysis_horizon,
    dimensions = dimensions,
    ignore_horizon=False,
)
plot_ltv_roi(ltv, ltv_history, roi, roi_history, analysis_horizon, window=7)

Вот и вторая проблема. Пользователи, пришедшие из TipTop, FaceBoom и AdNonSense не окупаются, их ROI меньше 100%. Из расчетов выше, мы знаем, что на TipTop и FaceBoom приходится большая часть бюджета на маркетинг. С Июня месяца стоимость привлечения пользователей из TipTop стала сильно дороже, а их ROI чуть больше 50%, что, наверняка, несет много убытков комании.

Вероятно, стоит присмотреться к привлечению пользователей из YRabbit, которые окупаются во много раз, или к пользователям из lambdaMediaAds, чье LTV значительно выше остальных

Узнаем, в чём причина неокупаемости по вышеуказанным проблемным каналам привлечения: в низкой конверсии или низком удержании.

conversion_raw, conversion, conversion_history = get_conversion(profiles, 
                                                                orders, 
                                                                observation_date, 
                                                                analysis_horizon, 
                                                                dimensions = dimensions,
                                                                ignore_horizon=False)
plot_conversion(conversion, conversion_history, analysis_horizon, window=7)

retention_raw, retention, retention_history = get_retention(profiles, 
                                                            visits, 
                                                            observation_date, 
                                                            analysis_horizon, 
                                                            dimensions = dimensions,
                                                            ignore_horizon=False)
plot_retention(retention, retention_history, analysis_horizon, window=7)

Наши проблемные FaceBoom и AdNonSense показывают очень хорошую конверсию, но в то же время у них катастрофически низкое удержание. У TipTop такой проблемы нет, конверсия и удержание на нормальном уровне.

Вывод

Проанализировав показатели конверсии, удержании, LTV, CAC и ROI в целом и в разбивке по стране, источнику и устройству первого посещения обнаружились следующие вероятные проблемы в маркетинговой политике компании:

По типу устройства:

• Во всех категориях LTV примерно одинаков, но привлечение пользователей продукции Apple значительно дороже, чем пользователей Android или ПК. Это может быть связано с разбивкой по регионам, т.к. в США большинство людей пользуется продукцией Apple.

По региону:

• В Июне сильно увеличились расходы на пользователей из США. Несмотря на их высокую конверсию и LTV, удержание заметно ниже по сравнению с пользователями из других стран. В следствие этих факторов ROI не достигает 100% и компания недополучает прибыль.

По каналу привлечения:

• Не окупаются пользователи, пришедшие из TipTop, FaceBoom и AdNonSense.
  • Первое объясняется тем, что в Июне резко увеличились расходы для данного источника трафика, а стоимость привлечения одного пользователя значительно выше чем из других источников. В результате ROI чуть больше 50%. Это очень дорогой канал привлечения, который не окупается.
  • Что касается пользователей, привлеченных из FaceBoom и AdNonSense, ROI также не переваливает за 100%. Эти клиенты имеют хорошие показатели конверсии, но в то же время и крайне низкое удержание. Можно сказать, что пользователи из этих источников низкого качества и быстро уходят.

Итого мы видим, что у пользователей из США и из источников FaceBoom и AdNonSense довольно хорошие показатели конверсии, но удержание сильно проседает. Трафик из данных каналов нам не подходят, пользователи не готовы продолжительно платить за наш продукт. Также стоит пересмотреть политику в отношении затрат на TipTop. Привлечение одного пользователя слишком дорогое, а его LTV ничем не примечательна, в следствие чего пользователь оттуда совершенно невыгоден. Это дорогой канал, тянущий деньги.

Я бы рекомендовал обратить внимание на пользователей из европейских стран, которые в данный момент окупаются уже через неделю и удерживаются лучше Американцев. В части источников трафика стоит посмотреть на такие каналы как:

• YRabbit
• MediaTornado
• lambdaMediaAds
• LeapBob
• RocketSuperAds

Пользователи с этих каналов более высокого качества и стабильно окупаются более чем в два раза, а зачастую и значительно больше